﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Diagnostics;
using System.IO;
using System.Xml;
using System.Collections;
using System.Data.SqlClient;
using System.Data;
using System.Threading;
using System.Data.OleDb;

namespace CAD_Project_1
{
    class DatabaseScripts
    {
        private static SqlConnection conn;        
        private static Initiate ini;
        private List<string> listAux = new List<string>();
        private DateTime today = DateTime.Now;
        private int year;
        private int month;
        private int day;
        private int counterSQLFile = 0;
        private int counterXML = 0;
        private int limit = 1000;
        private TextWriter sqlFile;
        private string sqlInsert;
        private Boolean active = true;
        private Thread t1;
        private Thread t2;        
        private StringBuilder buf;
        private StringBuilder bufSQL;
        private Stopwatch counterTime = new Stopwatch();
        
        public DatabaseScripts(Initiate init)
        {
            ini = init;
            year = today.Year;
            month = today.Month;
            day = today.Day;
            t1 = new Thread(new ThreadStart(generate_SQLInsertScript));
            t2 = new Thread(new ThreadStart(write_FichSQL));    

            if (Directory.Exists(ini.PName))
            {
                sqlFile = new StreamWriter(ini.PName + "\\" + ini.XmlFile.Replace("gz", "") + ".sql");
            }
            else 
            {
                sqlFile = new StreamWriter(ini.XmlFile.Replace("gz", "") + ".sql");
            }
            buf = new StringBuilder();
            bufSQL = new StringBuilder();                         
        }

        public void createDB(SqlCommand cmd)
        {
            StreamWriter s = new StreamWriter("script_bd_"+DateTime.Today.Year+
            DateTime.Today.Month+DateTime.Today.Day+".sql");
            
            string sqlCreateDBQuery = " CREATE DATABASE "
                       + ini.PName
                       + " ON PRIMARY "
                       + " (NAME = " + ini.PName + "_dat, "
                       + " FILENAME = '" + "C:\\Program Files\\Microsoft SQL Server\\MSSQL10.MSSQLSERVER\\MSSQL\\DATA\\" + ini.PName + "dat.mdf" + "', "
                       + " SIZE = 3MB,"
                       + " FILEGROWTH = " + "5" + ") "
                       + " LOG ON (NAME = '" + ini.PName + "_log'" + ", "
                       + " FILENAME = '" + "C:\\Program Files\\Microsoft SQL Server\\MSSQL10.MSSQLSERVER\\MSSQL\\DATA\\" + ini.PName + "log.ldf" + "', "
                       + " SIZE = 1MB, "
                       + " FILEGROWTH = " + "5MB" + ") ";
            s.WriteLine(sqlCreateDBQuery);
            s.Flush();
            s.Close();
            cmd = new SqlCommand(sqlCreateDBQuery, conn);
            cmd.ExecuteNonQuery();
        }

        public void createTablesDB(SqlCommand cmd)
        {
            StreamWriter s = new StreamWriter("script_tables_" + DateTime.Today.Year +
            DateTime.Today.Month + DateTime.Today.Day + ".sql");

            String script = " USE [" + ini.PName + "] " +
                    " SET ANSI_NULLS ON " +
                    " SET QUOTED_IDENTIFIER ON " +
                    " SET ANSI_PADDING ON " +
                    " CREATE TABLE [dbo].[t_project]( " +
                        "[name] [varchar](100) NULL, " +
                        "[url] [varchar](100) NULL, " +
                        "[last_update_date] [varchar](10) NULL, " +
                    ") ON [PRIMARY] " +
                    "SET ANSI_PADDING OFF " +
                    "SET ANSI_NULLS ON " +
                    "SET QUOTED_IDENTIFIER ON " +
                    "SET ANSI_PADDING ON " +
                    "CREATE TABLE [dbo].[t_host]( " +
                        "[id] [int] NULL, " +
                        "[userid] [int] NULL, " +
                        "[total_credit] [real] NULL, " +
                        "[expavg_credit] [real] NULL, " +
                        "[expavg_time] [real] NULL, " +
                        "[p_vendor] [varchar](50) NULL, " +
                        "[p_model] [varchar](300) NULL, " +
                        "[os_name] [varchar](100) NULL, " +
                        "[os_version] [varchar](100) NULL, " +
                        "[coprocs] [varchar](150) NULL, " +
                        "[create_time] [varchar](30) NULL, " +
                        "[rpc_time] [varchar](30) NULL, " +
                        "[timezone] [varchar](10) NULL, " +
                        "[ncpus] [int] NULL, " +
                        "[p_fpops] [varchar](50) NULL, " +
                        "[p_iops] [varchar](50) NULL, " +
                        "[p_membw] [varchar](50) NULL, " +
                        "[m_nbytes] [varchar](50) NULL, " +
                        "[m_cache] [varchar](50) NULL, " +
                        "[m_swap] [varchar](50) NULL, " +
                        "[d_total] [varchar](50) NULL, " +
                        "[d_free] [varchar](50) NULL, " +
                        "[n_bwup] [varchar](300) NULL, " +
                        "[n_bwdown] [varchar](max) NULL, " +
                        "[avg_turnaround] [varchar](50) NULL, " +
                        "[credit_per_cpu_sec] [varchar](50) NULL, " +
                        "[host_cpid] [varchar](50) NULL, " +
                        "[creation_month] [int] NULL, " +
                        "[creation_year] [int] NULL " +
                    ") ON [PRIMARY] " +
                    "SET ANSI_PADDING OFF ";

            s.WriteLine(script);
            s.Flush();
            s.Close();

            cmd = new SqlCommand(script, conn);
            cmd.ExecuteNonQuery();

            //insert na tabela project
            cmd = new SqlCommand("INSERT INTO [" + ini.PName + "].[dbo].[t_project] ([name],[url],[last_update_date]) " +
                "VALUES ('" + ini.PName + "','" + /*ini.UURL*/"_" + "','" + day + "-" + month + "-" + year + "');", conn);
            cmd.ExecuteNonQuery();                 
        }

        public void generate_SQLDB_TableScript()
        {
            if (!ini.PTest)
            {
                connectDB();
                //verifica se BD existe
                SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM sys.databases WHERE name = '" + ini.PName + "'", conn); //SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_NAME='t_host'", conn);
                object obj = cmd.ExecuteScalar();
                if (int.Parse(obj.ToString()) > 0)//se existir
                {
                    Console.WriteLine("Already exist " + obj.ToString() + " BD '" + ini.PName + "' into the server!");
                }
                else //se nao existir cria bd e tabelas
                {
                    //cria BD
                    createDB(cmd);

                    Console.WriteLine("Table created...");
                    //cria tabelas na bd
                    createTablesDB(cmd);
                }
                //faz inserts                                
                counterTime.Start();
                
                t1.Start();
                t2.Start();
                t1.Join();
                t2.Join();
                counterTime.Stop();

                string totalTime = "Total Time " +
                        counterTime.ElapsedMilliseconds / 1000 + " Seconds.";
                string numXML = "XML: " + ini.FFile +
                    ".xml\tNumber of lines in XML: " + counterXML;
                string numSQL = "SQL: " + ini.XmlFile +
                    ".sql\tNumber of lines in SQL: " + counterSQLFile;

                Console.WriteLine(totalTime);
                Console.WriteLine(numXML);
                Console.WriteLine(numSQL);
                ini.writeLog(totalTime);
                ini.writeLog(numXML);
                ini.writeLog(numSQL);
                sqlFile.Close();
                conn.Close();
            }
            else 
            {
                Console.WriteLine("Total Time: 0 Seconds.");
                Console.WriteLine("XML: " + ini.FFile +
                    ".xml\tNumber of lines in XML: 0");
                Console.WriteLine("SQL: " + ini.XmlFile +
                    ".sql\tNumber of lines in SQL: 0");
            }
        }

        public String scriptInsertDB(List<string> listaAux)
        {
            return "Insert Into ["+ini.PName+"].[dbo].[t_host] (" +
                                               "[id], " +
                                               "[userid], " +
                                               "[total_credit], " +
                                               "[expavg_credit], " +
                                               "[expavg_time], " +
                                               "[p_vendor], " +
                                               "[p_model], " +
                                               "[os_name], " +
                                               "[os_version], " +
                                               "[coprocs], " +
                                               "[create_time], " +
                                               "[rpc_time], " +
                                               "[timezone], " +
                                               "[ncpus], " +
                                               "[p_fpops], " +
                                               "[p_iops], " +
                                               "[p_membw], " +
                                               "[m_nbytes], " +
                                               "[m_cache], " +
                                               "[m_swap], " +
                                               "[d_total], " +
                                               "[d_free], " +
                                               "[n_bwup], " +
                                               "[n_bwdown], " +
                                               "[avg_turnaround], " +
                                               "[credit_per_cpu_sec], " +
                                               "[host_cpid], " +
                                               "[creation_month], " +
                                               "[creation_year] " +
                                               ") Values (" +
                                               listaAux[0] + ", " +
                                               listaAux[1] + ", " +
                                               listaAux[2] + ", " +
                                               listaAux[3] + ", " +
                                               listaAux[4] + ", '" +
                                               listaAux[5] + "', '" +
                                               listaAux[6] + "', '" +
                                               listaAux[7] + "', '" +
                                               listaAux[8] + "', '" +
                                               listaAux[9] + "', '" +
                                               listaAux[10] + "', '" +
                                               listaAux[11] + "', '" +
                                               listaAux[12] + "', '" +
                                               listaAux[13] + "', '" +
                                               listaAux[14] + "', '" +
                                               listaAux[15] + "', '" +
                                               listaAux[16] + "', '" +
                                               listaAux[17] + "', '" +
                                               listaAux[18] + "', '" +
                                               listaAux[19] + "', '" +
                                               listaAux[20] + "', '" +
                                               listaAux[21] + "', '" +
                                               listaAux[22] + "', '" + //string na bd
                                               listaAux[23] + "', '" + //string na bd
                                               listaAux[24] + "', '" +
                                               listaAux[25] + "', '" +
                                               listaAux[26] + "', " +
                                               month + ", " +
                                               year + ");";
        }

        public void generate_SQLInsertScript()
        {            
            try
            {
                XmlTextReader reader;
                if(File.Exists(ini.PName + "\\host"))
                     reader = new XmlTextReader(ini.PName+"\\"+ini.FFile.Replace(".gz",""));//C:\\SVNTortoise\\CAD Project 1\\bin\\Debug\\host");  //SETI_host QMC_host host xx.xml ");
                else 
                    reader = new XmlTextReader(ini.FFile.Replace(".gz",""));//C:\\SVNTortoise\\CAD Project 1\\bin\\Debug\\host");  //SETI_host QMC_host host xx.xml ");                                                
                Boolean checkInner = false;  //para verificar se tem inner text

                Console.Write("["+ini.FFile+".xml]\nStarting parsing: "+today+"\n");
                
                while (reader.Read())
                {
                    if (reader.NodeType == XmlNodeType.Element && 
                       (!reader.Name.Equals("userid") && listAux.Count == 1 || !reader.Name.Equals("coprocs") && listAux.Count == 9))
                    {
                        listAux.Add("0");
                    }
                    else if (reader.NodeType == XmlNodeType.Text)
                    {
                        checkInner = true;
                        listAux.Add(reader.Value.ToString().ToLower());//minusculas
                    }
                    else if (reader.NodeType == XmlNodeType.EndElement)
                    {                       
                        if (!checkInner) //se não tem inner text adicionar
                        {
                            listAux.Add("0");
                        }
                        checkInner = false;
                        if (reader.Name.Equals("host"))//fim do elemento host
                        {
                            try
                            {
                                sqlInsert = scriptInsertDB(listAux);                                
                                Monitor.Enter(this);
                                buf.AppendLine(sqlInsert);                                
                                Monitor.Exit(this);
                                Thread.Sleep(0);                                                    
                                counterSQLFile++;                               
                            }
                            catch (Exception e) 
                            { 
                                Console.WriteLine(e + "\n..ERRO - id: " +
                                    listAux[0]); 
                            }                            
                            listAux.Clear();
                        }
                    }                    
                    if (counterSQLFile == limit)
                    {
                        limit += 1000;
                        Console.WriteLine("Record #" + counterSQLFile + ": " +
                            counterTime.ElapsedMilliseconds / 1000 + 
                            " Seconds.");
                    }
                }
                counterXML = reader.LineNumber;
            }
            catch (XmlException e)
            {
                Console.WriteLine("Error: " + e.Message);                
            }
            active = false;                     
        }

        private void write_FichSQL(){            
            while (active)
            {                    
                    Thread.Sleep(75);  
                if (buf.Length != 0)
                {
                    Monitor.Enter(this);
                    sqlFile.Write(buf.ToString());
                    sqlFile.WriteLine(sqlInsert);
                    sqlFile.Flush();
                    insertDB();                                                         
                    buf.Length = 0;
                    Monitor.Exit(this);
                }                
            }
            if (buf.Length != 0)
            {
                sqlFile.Write(buf);
                sqlFile.Flush();
                buf.Remove(0, buf.Length);
                insertDB();
            }
            sqlFile.Close();
        }

        //Lê o ficheiro SQL linha a linha e executa-o (inserindo na BD)
        public void insertDB()
        {
                        try
                        {
                            SqlCommand cmd = new SqlCommand(buf.ToString(), conn);
                            cmd.ExecuteNonQuery();
                        }
                        catch (Exception e)
                        {
                            StreamWriter log;
                            if (File.Exists(ini.Path))
                            {
                                log = new StreamWriter(ini.Path, true);
                                log.WriteLine(buf.ToString());
                            }
                            else
                            {
                                log = new StreamWriter(ini.Path);
                                log.WriteLine(buf.ToString());
                            }
                            Console.WriteLine("SQL Insert error, " +
                            "see log file ");
                            log.Close();
                        }          
        }

        private static string createConnectionString()
        {
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder["Data Source"] = "ESTG-WORK";            
            builder.IntegratedSecurity = true;
            builder["Initial Catalog"] = "master";   //nome da DB
            builder.ConnectTimeout = 1000000;
            return builder.ConnectionString;
        }        

        private static void connectDB(){//resebe nome da bd
            conn = new SqlConnection(); //global                        
            try
            {
                conn.ConnectionString = createConnectionString();                
                conn.Open();                
                Console.WriteLine("Sucessfuly etablished connection.");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error connection\n" + ex.ToString());
                conn.Close();
            }
        }     
    }
}
